package db;
import model.*;
import java.sql.*;
import java.util.ArrayList;



public class DBSupplier implements IFDBSup{
    private  Connection con;
    /** Creates a new instance 
     * of DBSupplier */
    public DBSupplier() {
      con = DBConnection.getInstance().getDBcon();
    }
  //Implements the methods from the interface
    // get all Suppliers
    public ArrayList<Supplier> getAllSuppliers(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
    //get one Supplier having the phoneNo
    public Supplier findSupplier(String phoneNo, boolean retriveAssociation)
    {   String wClause = "  phoneNo = '" + phoneNo + "'";
        return singleWhere(wClause, retriveAssociation);
    }
    //find one Supplier having the name
    public Supplier searchSupplierName(String attValue, boolean retriveAssociation)
    {
        String wClause = "name like '%" + attValue + "%'";
        System.out.println("SearchSupplier " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
      
      //find one Supplier having the phoneNo
    public Supplier searchSupplierPhoneNo(String attValue, boolean retriveAssociation)
    {
        String wClause = "phoneNo = '" + attValue + "'";
        System.out.println("Search Supplier: " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
     //insert a new employee
    public int insertSupplier(Supplier sup)
    {  
        
  
       int rc = -1;
	   String query="INSERT INTO Supplier(name, address, country,phoneNo,email)  VALUES('"+
	  		sup.getName()  	  + "','"  	+
		    sup.getAddress()  + "','"   +
		    sup.getCountry()  + "','"   +
		    sup.getPhoneNo()  + "','"   +
		    sup.getEmail()  + "',',5)";
                     
           
      System.out.println("insert : " + query);		  					
      try{ // insert new Supplier
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}//end try	
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in Supplier db: "+ex);
	   }
	   return(rc);
	}
	
	public int updateSupplier(Supplier sup)
	{
		Supplier supObj  = sup;
		int rc=-1;
	  
		String query="UPDATE Supplier SET "+
		 	  "name ='"+ supObj.getName()+"', "+
		 	  "address ='"+ supObj.getAddress() + "' " +
		          " WHERE phoneNo = '"+ supObj.getPhoneNo() + "'";
                System.out.println("Update query:" + query);
  		try{ // update employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);
                      
	 	 	stmt.close();
		}//slut try	
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in Supplier db: "+ex);
	  	}
		return(rc);
	}
	
	public int delete(String phoneNo)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM Supplier WHERE phoneNo = '" +
	  		phoneNo + "'";
                System.out.println(query);
	  	try{ // delete from Supplier
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in Supplier db: "+ex);
   	        }
		return(rc);
	}
	
	//private methods
        //michWere is used whenever we want to select more than one employee
	
	 
	private ArrayList<Supplier> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Supplier> list = new ArrayList<Supplier>();	
		
		String query =  buildQuery(wClause);
  
  		try{ // read the Supplier from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
			int snr=0;
			while( results.next() ){
				Supplier supObj = new Supplier();
			 supObj = buildSupplier(results);	
                         list.add(supObj);	
			}//end while
                        // if retrieveAssociation is tru, the supervisor and department
                        // has to be selected for each object in the list
			stmt.close();
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	//Singelwhere is used when we only select one employee 	
	private Supplier singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Supplier supObj = new Supplier();
        supObj.setPhoneNo("");
	    String query =  buildQuery(wClause);
        System.out.println(query);
		try{ // read the Supplier from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            supObj = buildSupplier(results);
                            //assocaition is to be build
                            stmt.close();
//                            if(retrieveAssociation)
//                            {   //The supervisor and department is to be build as well
//                                String superssn = empObj.getSupervisor().getSsn();
//                                Employee superEmp = singleWhere(" ssn = '" + superssn + "'",false);
//                                empObj.setSupervisor(superEmp);
//                                System.out.println("Supervisor er hentet");
//                                //here the department has to be build
//                                
//                            }
			}
		
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return supObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM Supplier";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	//method to build an Supplier object
	private Supplier buildSupplier(ResultSet results)
      {   Supplier supObj = new Supplier();
          try{
                supObj.setName(results.getString(1));
                supObj.setAddress(results.getString(2));
                supObj.setCountry(results.getString(3));
                supObj.setPhoneNo(results.getString(4));
                supObj.setEmail(results.getString(5));
                
          }
         catch(Exception e)
         {
             System.out.println("error in building the Supplier object");
         }
         return supObj;
      }
 
}  
    

